package org.example.student_dormitory.dao.impl;

import org.example.student_dormitory.dao.DormitoryDao;
import org.example.student_dormitory.entity.Building;
import org.example.student_dormitory.entity.Dormitory;
import org.example.student_dormitory.utils.DBUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class DormitoryDaoImpl implements DormitoryDao {

    @Override
    public List<Dormitory> selectAll(int page, int pageSize) {
        List<Dormitory> list = new ArrayList<>();
        String sql = "select d.*,b.name as buildingName from dormitory d left join building b on d.buildingId = b.id where d.dr=0 or d.dr=2 " +
                " order by b.name desc limit ?,?";
        page = (page-1)*pageSize;
        List<Map<String, Object>> queryList = DBUtils.queryList(sql, page, pageSize);
//        System.out.println("4准备在Dao中进行判断");
        if (queryList == null || queryList.size() == 0){
            return null;
        }else {
            int count = selectCount();
            for (int i = 0; i < queryList.size(); i++) {
                Dormitory dormitory = new Dormitory();
                dormitory.setId(Long.parseLong(queryList.get(i).get("id").toString()));
                dormitory.setName(queryList.get(i).get("name").toString());
                dormitory.setNumber(Long.parseLong(queryList.get(i).get("number").toString()));
                dormitory.setSparebed(Long.parseLong(queryList.get(i).get("sparebed").toString()));
//                System.out.println(queryList.get(i).get("buildingName"));
                dormitory.setBuildingName(queryList.get(i).get("buildingName").toString());
                dormitory.setBuildingId(Long.parseLong(queryList.get(i).get("buildingId").toString()));
//                dormitory.setDr(Long.parseLong(queryList.get(i).get("dr").toString()));
//                System.out.println(count);
                dormitory.setTotalCount(count);
//                System.out.println(dormitory.getTotalCount());
//                System.out.println("5向service返回dormitory对象");
                list.add(dormitory);
            }
            return list;
        }
    }

    @Override
    public List<Dormitory> selectAll(int page, int pageSize, String key, String value) {
        List<Dormitory> list = new ArrayList<>();
        //分页计算
        page = (page-1)*pageSize;
        //模糊查询条件拼接
        value = "%" + value + "%";
        if ("buildingName".equals(key)){
            //条件为楼栋名
            String sql1 = "select id from building where name like ? and dr=0";
            List<Map<String, Object>> queryList = DBUtils.queryList(sql1,value);
            if (queryList.size() ==0 || queryList == null){
                return null;
            }else {
                List<Integer> buildingIdList = new ArrayList<>();
                for (int i = 0; i < queryList.size(); i++) {
                    buildingIdList.add(Integer.parseInt(queryList.get(i).get("id").toString()));
                }
                //拼接buildingId
                String reg = "";
                for (int i = 0; i < buildingIdList.size(); i++) {
                    reg += buildingIdList.get(i);
                    if (i != buildingIdList.size()-1){
                        reg += ",";
                    }
                }
                //根据楼栋Id找出对应的宿舍信息
                String sql2 = "select d.*,b.name as buildingName from dormitory d left join building b on d.buildingId = b.id " +
                        " where d.buildingId in (?) and (d.dr=0 or d.dr=2) order by b.name desc limit ?,?";
                List<Map<String, Object>> queryList1 = DBUtils.queryList(sql2, reg,page,pageSize);
                if (queryList1 == null || queryList1.size() == 0){
                    return null;
                }else {
                    //获取总数
                    String sql = "select count(*) from dormitory where buildingId in (?) and (dr=0 or dr=2)";
                    Map<String, Object> stringObjectMap = DBUtils.qureyOne(sql, value);
                    int count = Integer.parseInt(stringObjectMap.get("count(*)").toString());

                    for (int i = 0; i < queryList1.size(); i++) {
                        Dormitory dormitory = new Dormitory();
                        dormitory.setId(Long.parseLong(queryList1.get(i).get("id").toString()));
                        dormitory.setName(queryList1.get(i).get("name").toString());
                        dormitory.setNumber(Long.parseLong(queryList1.get(i).get("number").toString()));
                        dormitory.setSparebed(Long.parseLong(queryList1.get(i).get("sparebed").toString()));
//                System.out.println(queryList.get(i).get("buildingName"));
                        dormitory.setBuildingName(queryList1.get(i).get("buildingName").toString());
                        dormitory.setBuildingId(Long.parseLong(queryList1.get(i).get("buildingId").toString()));
//                dormitory.setDr(Long.parseLong(queryList.get(i).get("dr").toString()));
//                System.out.println(count);
                        dormitory.setTotalCount(count);
//                System.out.println(dormitory.getTotalCount());
//                System.out.println("5向service返回dormitory对象");
                        list.add(dormitory);
                    }
                    return list;
                }
            }
        }else {
            if ("dormitoryType".equals(key)){
                key = "number";
            }
            String sql = "select d.*,b.name as buildingName from dormitory d left join building b on d.buildingId = b.id" +
                    " where d."+key+" like ? and (d.dr=0 or d.dr=2) order by b.name desc limit ?,?";
            List<Map<String, Object>> queryList = DBUtils.queryList(sql, value, page, pageSize);
            //获取总数居
            int count = selectCount(key, value);
            for (int i = 0; i < queryList.size(); i++) {
                Dormitory dormitory = new Dormitory();
                dormitory.setId(Long.parseLong(queryList.get(i).get("id").toString()));
                dormitory.setName(queryList.get(i).get("name").toString());
                dormitory.setNumber(Long.parseLong(queryList.get(i).get("number").toString()));
                dormitory.setSparebed(Long.parseLong(queryList.get(i).get("sparebed").toString()));
//                System.out.println(queryList.get(i).get("buildingName"));
                dormitory.setBuildingName(queryList.get(i).get("buildingName").toString());
                dormitory.setBuildingId(Long.parseLong(queryList.get(i).get("buildingId").toString()));
//                dormitory.setDr(Long.parseLong(queryList.get(i).get("dr").toString()));
//                System.out.println(count);
                dormitory.setTotalCount(count);
//                System.out.println(dormitory.getTotalCount());
//                System.out.println("5向service返回dormitory对象");
                list.add(dormitory);
            }
            return list;
        }
    }

    @Override
    public int selectCount() {
        String sql = "select count(*) from dormitory where dr=0 or dr=2";
        Map<String, Object> stringObjectMap = DBUtils.qureyOne(sql);
        return Integer.parseInt(stringObjectMap.get("count(*)").toString());
    }

    @Override
    public int selectCount(String key, String value) {
        String sql = "select count(*) from dormitory where "+key+" like ? and (dr=0 or dr=2)";
        Map<String, Object> stringObjectMap = DBUtils.qureyOne(sql,value);
        return Integer.parseInt(stringObjectMap.get("count(*)").toString());
    }

    @Override
    public List<Building> selectBuildings() {
        List<Building> list = new ArrayList<>();
        String sql = "select id,name from building where dr=0";
        List<Map<String, Object>> queryList = DBUtils.queryList(sql);
        if (queryList == null || queryList.size() == 0) {
            return null;
        }else {
            for (int i = 0; i < queryList.size(); i++) {
                Building building = new Building();
                building.setId(Long.parseLong(queryList.get(i).get("id").toString()));
                building.setName(queryList.get(i).get("name").toString());
                list.add(building);
            }
            return list;
        }
    }

    @Override
    public boolean selectByBuildingIdAndName(String name, int buildingId) {
        String sql = "select * from dormitory where name=? and buildingId=? and dr=0";
        List<Map<String, Object>> queryList = DBUtils.queryList(sql, name, buildingId);
        if (queryList == null || queryList.size() == 0){
            //说名这个宿舍号可用
            return true;
        }
        return false;
    }

    @Override
    public boolean insertDormitory(Dormitory dormitory) {
        String sql = "insert into dormitory(name,number,sparebed,buildingId,dr) values(?,?,?,?,?)";
        try {
            return DBUtils.insert(sql, dormitory.getName(), dormitory.getNumber(), dormitory.getSparebed(), dormitory.getBuildingId(), dormitory.getDr());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean update(Dormitory dormitory) {
        String sql = "update dormitory set name=?, sparebed=?, number=? where id=?";
        try {
            System.out.println("在Dao中准备返回结果");
            return DBUtils.update(sql,dormitory.getName(),dormitory.getSparebed(),dormitory.getNumber(),dormitory.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Boolean deleteById(int id) {
        String sql = "update dormitory set dr=1 where id=?";
        try {
            return DBUtils.update(sql,id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
